package com.yhty.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.yhty.model.WeMonth;
import com.yhty.utils.DateUtil;

/**
 * 水电每月模型dao
 * @author RoySong
 * 2011-3-31-下午07:39:30
 */
@Repository("weMonthDao")
public class WeMonthDao {

	private JdbcTemplate jdbcTemplate;

	@Resource(name="jdbcTemplate")
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	/**
	 * 本月是否水电初始化
	 * @return
	 */
	public boolean isWeInit(){
		int[] today = DateUtil.getYearMonthDay(null);
		String sql = "select count(*) from init_status where "+
		" year(we_date)="+today[0]+" and month(we_date)="+today[1];
		int i = jdbcTemplate.queryForInt(sql);
		if(i == 0)
			return false;
		return true;
	}
	
	/**
	 * 标识本月水电已初始化
	 */
	public void init(){
		String sql = "update init_status set we_date='"
			+DateUtil.getToday()+"'";
		jdbcTemplate.update(sql);
	}

	/**
	 * 取得当月初始水电费用信息(水量和出租关系ID)
	 * @return
	 */
	public List<WeMonth> getMonth() {
		String sql = "select h.use_area,r.id from rent r,"+
		"house h where r.status=1 and r.file_code=h.file_code";
		return jdbcTemplate.query(sql, new RowMapper<WeMonth>(){

			public WeMonth mapRow(ResultSet rs, int rowNum) throws SQLException {
				WeMonth wm = new WeMonth();
				wm.setRent_id(rs.getLong("id"));
				wm.setE_num(new Float(0));
				wm.setW_num(rs.getFloat("use_area"));
				return wm;
			}
			
		});
	}

	/**
	 * 批量添加水电费用信息
	 * @param wmList
	 */
	public void batchAdd(final List<WeMonth> wmList) {
		String sql = "insert into wemonth values(WEMONTH_SEQ_ID.NEXTVAL" +
				",?,?,?,?,?,?)";
		jdbcTemplate.batchUpdate(sql, 
				new BatchPreparedStatementSetter(){

					public int getBatchSize() {
						return wmList.size();
					}

					public void setValues(PreparedStatement ps, int i)
							throws SQLException {
						ps.setFloat(1, wmList.get(i).getW_price());
						ps.setFloat(2, wmList.get(i).getE_price());
						ps.setFloat(3, wmList.get(i).getW_num());
						ps.setFloat(4, wmList.get(i).getE_num());
						ps.setString(5, DateUtil.getToday());
						ps.setLong(6, wmList.get(i).getRent_id());
					}
			
		});
	}
	
	/**
	 * 按月取出水电费用信息
	 * @param date
	 * @return
	 */
	public List<WeMonth> getMonth(String dateStr){
		int[] date = DateUtil.getYearMonthDay(dateStr);
		String sql = "select * from wemonth where "+
		"year(create_date)="+date[0]+" and "+
		"month(create_date)="+date[1];
		return jdbcTemplate.query(sql, new RowMapper<WeMonth>(){

			public WeMonth mapRow(ResultSet rs, int rowNum) throws SQLException {
				WeMonth wm = new WeMonth();
				wm.setWe_id(rs.getLong("we_id"));
				wm.setCreate_date(DateUtil.getDate(rs.getTimestamp("create_date")));
				wm.setE_num(rs.getFloat("e_num"));
				wm.setE_price(rs.getFloat("e_price"));
				wm.setRent_id(rs.getLong("rent_id"));
				wm.setW_num(rs.getFloat("w_num"));
				wm.setW_price(rs.getFloat("w_price"));
				return wm;
			}
			
		});
	}

	/**
	 * 批量更形水电单价信息
	 * @param wList
	 */
	public void batchUpdate(final List<WeMonth> wList) {
		String sql = "update wemonth set w_price=?,e_price=?" +
				" where we_id=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){

			public int getBatchSize() {
				return wList.size();
			}

			public void setValues(PreparedStatement ps, int i)
					throws SQLException {
				ps.setFloat(1, wList.get(i).getW_price());
				ps.setFloat(2, wList.get(i).getE_price());
				ps.setLong(3, wList.get(i).getWe_id());
			}
			
		});
	}

	/**
	 * 更新水电信息
	 * @param wm
	 */
	public void update(WeMonth wm) {
		String sql = "update wemonth set w_price="+wm.getW_price()
		+",e_price="+wm.getE_price()+",w_num="+wm.getW_num()
		+",e_num="+wm.getE_num()+",create_date='"+DateUtil.getToday()
		+"' where we_id="+wm.getWe_id();
		jdbcTemplate.update(sql);
	}

	/**
	 * 根据租房关系和日期获取水电信息
	 * @param rentId
	 * @param today
	 * @return
	 */
	public WeMonth getByRentId(Long rentId, String today) {
		int[] date = DateUtil.getYearMonthDay(today);
		String sql = "select * from wemonth where rent_id="+
		rentId+" and year(create_date)="+date[0]+" and"+
		" month(create_date)="+date[1];
		return jdbcTemplate.queryForObject(sql, new RowMapper<WeMonth>(){

			public WeMonth mapRow(ResultSet rs, int rowNum) throws SQLException {
				WeMonth wm = new WeMonth();
				wm.setWe_id(rs.getLong("we_id"));
				wm.setCreate_date(DateUtil.getDate(rs.getTimestamp("create_date")));
				wm.setE_num(rs.getFloat("e_num"));
				wm.setE_price(rs.getFloat("e_price"));
				wm.setRent_id(rs.getLong("rent_id"));
				wm.setW_num(rs.getFloat("w_num"));
				wm.setW_price(rs.getFloat("w_price"));
				return wm;
			}
			
		});
	}
}
